package org.example.springboot.mapper;

import org.apache.ibatis.annotations.*;
import org.example.springboot.entity.Need;
import org.example.springboot.entity.UserAndNeed;

import java.util.List;

@Mapper
public interface NeedMapper {

    @Select("SELECT t.id, t.account, te.name, t.course, t.background, t.sex, t.salary, t.duration_begin, t.duration_end, t.content " +
            "FROM need t " +
            "JOIN user te ON t.account = te.account " +
            "WHERE te.name LIKE CONCAT('%', #{name}, '%') " +
            "AND t.course LIKE CONCAT('%', #{course}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    List<Need> findByNameAndCourse(@Param("name") String name,
                                   @Param("course") String course,
                                   @Param("offset") int offset,
                                   @Param("pageSize") int pageSize);


    @Select("SELECT t.id, t.account, t.course, t.background, t.sex, t.salary, t.duration_begin, t.duration_end, t.content, te.name " +
            "FROM need t " +
            "JOIN user te ON t.account = te.account " +
            "WHERE te.name LIKE CONCAT('%', #{name}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    List<Need> findByName(@Param("name") String name,
                           @Param("offset") int offset,
                           @Param("pageSize") int pageSize);

    @Select("SELECT t.id, t.account, t.course, t.background, t.sex, t.salary, t.duration_begin, t.duration_end, t.content, te.name " +
            "FROM need t " +
            "JOIN user te ON t.account = te.account " +
            "WHERE t.course LIKE CONCAT('%', #{course}, '%') " +
            "LIMIT #{offset}, #{pageSize}")
    List<Need> findByCourse(@Param("course") String course,
                             @Param("offset") int offset,
                             @Param("pageSize") int pageSize);


    @Select("SELECT COUNT(*) " +
            "FROM need t " +
            "JOIN user te ON t.account = te.account " +
            "WHERE te.name LIKE CONCAT('%', #{name}, '%') " +
            "AND t.course LIKE CONCAT('%', #{course}, '%')")
    int countByNameAndCourse(@Param("name") String name, @Param("course") String course);


    @Select("SELECT COUNT(*) " +
            "FROM need t " +
            "JOIN user te ON t.account = te.account " +
            "WHERE te.name LIKE CONCAT('%', #{name}, '%')")
    int countByName(@Param("name") String name);


    @Select("SELECT COUNT(*) " +
            "FROM need t " +
            "JOIN user te ON t.account = te.account " +
            "WHERE t.course LIKE CONCAT('%', #{course}, '%')")
    int countByCourse(@Param("course") String course);

    @Select("SELECT n.account, u.avatar, u.name, n.background, n.sex, n.content, n.salary " +
            "FROM need n " +
            "JOIN user u ON n.account = u.account " +
            "WHERE n.course = #{course}")
    List<UserAndNeed> selectByCourse(String course);

    @Select("SELECT n.*, u.username, u.name, u.phone_number, u.city, u.avatar "
            + "FROM need n JOIN user u ON n.account = u.account "
            + "WHERE n.account = #{account} AND n.course = #{course}")
    UserAndNeed findNeedByAccountAndCourse(@Param("account") String account, @Param("course") String course);

    @Select("SELECT * FROM need JOIN user WHERE need.account = user.account")
    List<UserAndNeed> findAllNeed();

    @Select("SELECT * FROM need WHERE account = #{account}")
    List<Need> findNeedByAccount(@Param("account") String account);

    @Insert("INSERT INTO need (account, course, background, salary, duration_begin, duration_end, content) VALUES (#{account}, #{course}, #{background}, #{salary}, #{duration_begin}, #{duration_end}, #{content})")
    void insertNeed(Need need);

    @Delete("DELETE FROM need WHERE id = #{id}")
    void deleteNeed(@Param("id") int id);
}
